package model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import model.bean.VuAn;

public class VuAnDAO extends BaseDAO {

	public int getIDvuan() {
		openDatabase();
		qry = "SELECT idvuan FROM mockdb.vu_an ORDER BY idvuan DESC LIMIT 1";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			int id;
			while (rs.next()) {
				id = rs.getInt("idvuan");
				int idVuan = id + 1;
				return idVuan;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return 1;
	}

	public boolean themVuan(int idVuan, String idcb, String idchuyenan,
			String tenvuan, String noidung, String ngaykhoito,
			String ngaytiepnhan, String sobican, String tiendoDT,
			String thoihanDT1, String thoihanDT2, String thoihanTG1,
			String thoihanTG2) {

		boolean success = false;
		openDatabase();
		qry = "INSERT INTO mockdb.vu_an (idvuan, tenvuan, ngaytiepnhan, ngaykhoito, thoihanDT1, thoihanDT2, noidung,"
				+ " "
				+ " "
				+ " sobican, thoihanTG1, thoihanTG2, tiendoDT, chuyen_an_idchuyen_an, canbo_idCanBo) "
				+ " " + " " + "VALUES ('"
				+ idVuan
				+ "', '"
				+ tenvuan
				+ "', '"
				+ ngaytiepnhan
				+ "', '"
				+ ngaykhoito
				+ "', '"
				+ thoihanDT1
				+ "', '"
				+ thoihanDT2
				+ "', N'"
				+ noidung
				+ "', '"
				+ sobican
				+ "', '"
				+ thoihanTG1
				+ "', '"
				+ thoihanTG2
				+ "', "
				+ tiendoDT
				+ ", '"
				+ idchuyenan
				+ "', '" + idcb + "')";

		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			success = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return success;
	}

	public ArrayList<VuAn> dsVuAn() {
		ArrayList<VuAn> dsVuAn = new ArrayList<VuAn>();
		openDatabase();
		qry = "SELECT * FROM mockdb.vu_an inner join mockdb.chuyen_an on chuyen_an.idchuyen_an=vu_an.chuyen_an_idchuyen_an";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			while (rs.next()) {
				VuAn va = new VuAn();

				va.setIdVuan(rs.getString("idvuan"));
				va.setIdCanbo(rs.getString("canbo_idCanBo"));
				va.setIdChuyenan(rs.getString("chuyen_an_idchuyen_an"));
				va.setNgayKhoito(rs.getString("ngaykhoito"));
				va.setNgayTiepnhan(rs.getString("ngaytiepnhan"));
				va.setNoidung(rs.getString("noidung"));
				va.setSobican(rs.getString("sobican"));
				va.setThoihanDT1(rs.getString("thoihanDT1"));
				va.setThoihanDT2(rs.getString("thoihanDT2"));
				va.setThoihanTG1(rs.getString("thoihanTG1"));
				va.setThoihanTG2(rs.getString("thoihanTG2"));
				va.setTiendoDT(rs.getString("tiendoDT"));
				va.setTenVuan(rs.getString("tenvuan"));
				va.setTenChuyenan(rs.getString("tenchuyenan"));

				dsVuAn.add(va);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return dsVuAn;
	}

	public boolean xoaVuAn(String idva) {
		openDatabase();
		qry = "DELETE FROM mockdb.vu_an WHERE idvuan = '" + idva + "'";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public VuAn getVuan(String idva) {
		openDatabase();
		VuAn va = new VuAn();
		qry = "SELECT mockdb.vu_an.*, mockdb.canbo.TenCB, mockdb.chuyen_an.tenchuyenan FROM mockdb.vu_an "
				+ " "
				+ " "
				+ "inner join mockdb.chuyen_an on chuyen_an.idchuyen_an=vu_an.chuyen_an_idchuyen_an inner join mockdb.canbo on canbo.idCanBo = vu_an.canbo_idCanBo where idvuan = '"
				+ idva + "'";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			while (rs.next()) {

				va.setIdVuan(rs.getString("idvuan"));
				va.setIdCanbo(rs.getString("canbo_idCanBo"));
				va.setIdChuyenan(rs.getString("chuyen_an_idchuyen_an"));
				va.setNgayKhoito(rs.getString("ngaykhoito"));
				va.setNgayTiepnhan(rs.getString("ngaytiepnhan"));
				va.setNoidung(rs.getString("noidung"));
				va.setSobican(rs.getString("sobican"));
				va.setThoihanDT1(rs.getString("thoihanDT1"));
				va.setThoihanDT2(rs.getString("thoihanDT2"));
				va.setThoihanTG1(rs.getString("thoihanTG1"));
				va.setThoihanTG2(rs.getString("thoihanTG2"));
				va.setTiendoDT(rs.getString("tiendoDT"));
				va.setTenVuan(rs.getString("tenvuan"));
				va.setTenChuyenan(rs.getString("tenchuyenan"));
				va.setTenCanbo(rs.getString("TenCB"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return va;
	}

	public boolean suaVuan(String idva, String idcb, String idchuyenan,
			String tenvuan, String noidung, String ngaykhoito,
			String ngaytiepnhan, String sobican, String tiendoDT,
			String thoihanDT1, String thoihanDT2, String thoihanTG1,
			String thoihanTG2) {
		openDatabase();
		qry = "UPDATE mockdb.vu_an SET tenvuan=N'" + tenvuan
				+ "', ngaytiepnhan='" + ngaytiepnhan + "', ngaykhoito='"
				+ ngaykhoito + "', thoihanDT1='" + thoihanDT1
				+ "', thoihanDT2='" + thoihanDT2 + "', noidung='" + noidung
				+ "'," + " " + "" + " sobican='" + sobican + "', thoihanTG1='"
				+ thoihanTG1 + "', thoihanTG2='" + thoihanTG2 + "', tiendoDT="
				+ tiendoDT + ", chuyen_an_idchuyen_an='" + idchuyenan
				+ "', canbo_idCanBo='" + idcb + "' WHERE idvuan='" + idva + "'";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}
}
